import pandas as pd
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 读取数据
df = pd.read_csv('combined_table.csv')

# 提取年份信息
df['year'] = pd.to_datetime(df['create_time']).dt.year

# 筛选出各年的数据
df_2022 = df[df['year'] == 2022]
df_2023 = df[df['year'] == 2023]
df_2024 = df[df['year'] == 2024]

#df_2022.to_csv('df_2022.csv', index=False)
#df_2023.to_csv('df_2023.csv', index=False)
#df_2024.to_csv('df_2024.csv', index=False)

count_2022_1 = df_2022['hplx_x'].value_counts()[1]
count_2022_0 = df_2022['hplx_x'].value_counts()[0]

print(f"2022年矿粉的数量：{count_2022_1 }")
print(f"2022年水泥的数量：{count_2022_0}")

count_2023_1 = df_2023['hplx_x'].value_counts()[1]
count_2023_0 = df_2023['hplx_x'].value_counts()[0]

print(f"2023年矿粉的数量：{count_2023_1}")
print(f"2023年水泥的数量：{count_2023_0}")

count_2024_1 = df_2024['hplx_x'].value_counts()[1]
count_2024_0 = df_2024['hplx_x'].value_counts()[0]

print(f"2024年矿粉的数量：{count_2024_1}")
print(f"2024年水泥的数量：{count_2024_0}")

# 数据可视化
labels = ['水泥', '矿粉']
sizes_2022 = [count_2022_0, count_2022_1]
sizes_2023 = [count_2023_0, count_2023_1]
sizes_2024 = [count_2024_0, count_2024_1]

fig, axes = plt.subplots(1, 3, figsize=(12, 4))

axes[0].pie(sizes_2022, labels=labels, autopct='%1.1f%%')
axes[0].set_title('2022年')

axes[1].pie(sizes_2023, labels=labels, autopct='%1.1f%%')
axes[1].set_title('2023年')

axes[2].pie(sizes_2024, labels=labels, autopct='%1.1f%%')
axes[2].set_title('2024年')

plt.show()

#对月份筛选
df['month'] = pd.to_datetime(df['create_time']).dt.month

# 筛选出2022年和2023年的数据
df_2022 = df[df['year'] == 2022]
df_2023 = df[df['year'] == 2023]

# 统计2022年和2023年各月水泥和矿粉的销量
cement_sales_2022 = df_2022.groupby('month')['hplx_x'].apply(lambda x: (x == 0).sum()).reset_index(name='cement_sales_2022')
mineral_powder_sales_2022 = df_2022.groupby('month')['hplx_x'].apply(lambda x: (x == 1).sum()).reset_index(name='mineral_powder_sales_2022')

#cement_sales_2022.to_csv('cement_sales_2022.csv', index=False)
#mineral_powder_sales_2022.to_csv('mineral_powder_sales_2022.csv', index=False)

cement_sales_2023 = df_2023.groupby('month')['hplx_x'].apply(lambda x: (x == 0).sum()).reset_index(name='cement_sales_2023')
mineral_powder_sales_2023 = df_2023.groupby('month')['hplx_x'].apply(lambda x: (x == 1).sum()).reset_index(name='mineral_powder_sales_2023')

#cement_sales_2023.to_csv('cement_sales_2023.csv', index=False)
#mineral_powder_sales_2023.to_csv('mineral_powder_sales_2023.csv', index=False)

# 合并数据
sales_data = cement_sales_2022.merge(cement_sales_2023, on='month', how='outer').merge(mineral_powder_sales_2022, on='month', how='outer').merge(mineral_powder_sales_2023, on='month', how='outer')
sales_data = sales_data.fillna(0)

# 计算移动平均值
window_size = 12  # 移动平均的窗口大小
sales_data['cement_sales_avg'] = sales_data['cement_sales_2022'].rolling(window=window_size, min_periods=1).mean() + sales_data['cement_sales_2023'].rolling(window=window_size, min_periods=1).mean()
sales_data['mineral_powder_sales_avg'] = sales_data['mineral_powder_sales_2022'].rolling(window=window_size, min_periods=1).mean() + sales_data['mineral_powder_sales_2023'].rolling(window=window_size, min_periods=1).mean()

# 预测2024年全年水泥和矿粉的销量
predicted_cement_sales_2024 = sales_data['cement_sales_avg'].iloc[-1] * 12
predicted_mineral_powder_sales_2024 = sales_data['mineral_powder_sales_avg'].iloc[-1] * 12

print(f"预测2024年全年水泥的销量：{predicted_cement_sales_2024}")
print(f"预测2024年全年矿粉的销量：{predicted_mineral_powder_sales_2024}")

# 绘制水泥销量柱状图
plt.bar(sales_data['month'], sales_data['cement_sales_2022'], label='2022 年水泥销量')
plt.bar(sales_data['month'], sales_data['cement_sales_2023'], label='2023 年水泥销量')
plt.plot(sales_data['month'], sales_data['cement_sales_avg'], label='移动平均（水泥）', color='red')
plt.xlabel('月份')
plt.ylabel('水泥销量')
plt.title('水泥销量趋势')
plt.legend()
plt.show()

# 绘制矿粉销量柱状图
plt.bar(sales_data['month'], sales_data['mineral_powder_sales_2022'], label='2022 年矿粉销量')
plt.bar(sales_data['month'], sales_data['mineral_powder_sales_2023'], label='2023 年矿粉销量')
plt.plot(sales_data['month'], sales_data['mineral_powder_sales_avg'], label='移动平均（矿粉）', color='red')
plt.xlabel('月份')
plt.ylabel('矿粉销量')
plt.title('矿粉销量趋势')
plt.legend()
plt.show()

# 水泥销量图
plt.figure(figsize=(10, 6))
plt.plot(sales_data['month'], sales_data['cement_sales_2022'], label='2022年水泥销量')
plt.plot(sales_data['month'], sales_data['cement_sales_2023'], label='2023年水泥销量')
plt.plot(sales_data['month'], sales_data['cement_sales_avg'], label='水泥销量移动平均')
plt.xlabel('月份')
plt.ylabel('水泥销量')
plt.title('水泥销量趋势')
plt.legend()
plt.grid(True)
plt.show()

# 矿粉销量图
plt.figure(figsize=(10, 6))
plt.plot(sales_data['month'], sales_data['mineral_powder_sales_2022'], label='2022年矿粉销量')
plt.plot(sales_data['month'], sales_data['mineral_powder_sales_2023'], label='2023年矿粉销量')
plt.plot(sales_data['month'], sales_data['mineral_powder_sales_avg'], label='矿粉销量移动平均')
plt.xlabel('月份')
plt.ylabel('矿粉销量')
plt.title('矿粉销量趋势')
plt.legend()
plt.grid(True)
plt.show()